{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "704b1800",
   "metadata": {
    "colab_type": "text",
    "id": "view-in-github"
   },
   "source": [
    "<a href=\"https://colab.research.google.com/github/peremartra/Large-Language-Model-Notebooks-Course/blob/main/1-Introduction%20to%20LLMs%20with%20OpenAI/1_2b-Easy_NL2SQL.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "MDJ3_BRhasUG",
   "metadata": {
    "id": "MDJ3_BRhasUG"
   },
   "source": [
    "<div>\n",
    "<h1><a href=\"https://github.com/peremartra/Large-Language-Model-Notebooks-Course\">Large Language Models Projects</a></h1>\n",
    "    <h3>Apply and Implement Strategies for Large Language Models</h3>\n",
    "    <h2>Create a Simple Natural Language to SQL translator</h2>\n",
    "    contributions by <b>Fabricio Q</b>\n",
    "    \n",
    "</div>\n",
    "\n",
    "<br>\n",
    "\n",
    "<div>\n",
    "    &nbsp;\n",
    "    <a target=\"_blank\" href=\"https://www.linkedin.com/in/matiasquaglia\"><img src=\"https://img.shields.io/badge/style--5eba00.svg?label=LinkedIn&logo=linkedin&style=social\"></a>\n",
    "    \n",
    "</div>\n",
    "\n",
    "<br>\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ZVFLu27kVQor",
   "metadata": {
    "id": "ZVFLu27kVQor"
   },
   "source": [
    "This is the unofficial repository for the book:\n",
    "        <a href=\"https://amzn.to/4eanT1g\"> <b>Large Language Models:</b> Apply and Implement Strategies for Large Language Models</a> (Apress).\n",
    "        The book is based on the content of this repository, but the notebooks are being updated, and I am incorporating new examples and chapters.\n",
    "        If you are looking for the official repository for the book, with the original notebooks, you should visit the\n",
    "        <a href=\"https://github.com/Apress/Large-Language-Models-Projects\">Apress repository</a>, where you can find all the notebooks in their original format as they appear in the book."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c23b4dd1",
   "metadata": {
    "id": "c23b4dd1"
   },
   "source": [
    "# SQL Generator\n",
    "A sample of how to build a translator from natural language to SQL:\n",
    "\n",
    "* GPT 35\n",
    "* Panel\n",
    "* OpenAI\n",
    "\n",
    "Deeper explanations in the article: [How to Create a Natural Language to SQL Translator Using OpenAI API](https://medium.com/towards-artificial-intelligence/how-to-create-a-natural-language-to-sql-translator-using-openai-api-e1b1f72ac35a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1d00720",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "a1d00720",
    "outputId": "84107c89-10cd-423a-c8ee-fa97aa1916fd"
   },
   "outputs": [],
   "source": [
    "!pip install openai==1.1.1\n",
    "!pip install panel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a03f026a",
   "metadata": {
    "id": "a03f026a"
   },
   "outputs": [],
   "source": [
    "import openai\n",
    "import panel as pn\n",
    "#from mykeys import openai_api_key\n",
    "openai.api_key=\"your-openai-api-key\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77eac86d",
   "metadata": {
    "id": "77eac86d"
   },
   "outputs": [],
   "source": [
    "def continue_conversation(messages, temperature=0):\n",
    "    response = openai.chat.completions.create(\n",
    "        model=\"gpt-3.5-turbo\",\n",
    "        messages=messages,\n",
    "        temperature=temperature,\n",
    "    )\n",
    "    #print(str(response.choices[0].message[\"content\"]))\n",
    "    return response.choices[0].message.content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51bec475",
   "metadata": {
    "id": "51bec475"
   },
   "outputs": [],
   "source": [
    "def add_prompts_conversation(_):\n",
    "    #Get the value introduced by the user\n",
    "    prompt = client_prompt.value_input\n",
    "    client_prompt.value = ''\n",
    "\n",
    "    #Append to the context the User promnopt.\n",
    "    context.append({'role':'user', 'content':f\"{prompt}.\"})\n",
    "    context.append({'role':'system', 'content':f\"\"\"YOU ARE A SQL Assistant. \\\n",
    "    If the user ask for something that the result is not an SQL Order \\\n",
    "    just answer something nice and simple asking him, with maximum 10 words, for something that \\\n",
    "    can be solved with SQL.\"\"\"})\n",
    "\n",
    "    #Get the response.\n",
    "    response = continue_conversation(context)\n",
    "\n",
    "    #Add the response to the context.\n",
    "    context.append({'role':'assistant', 'content':f\"{response}\"})\n",
    "\n",
    "    #Undate the panels to shjow the conversation.\n",
    "    panels.append(\n",
    "        pn.Row('User:', pn.pane.Markdown(prompt, width=600)))\n",
    "    panels.append(\n",
    "        pn.Row('Assistant:', pn.pane.Markdown(response, width=600, styles={'background-color': '#F6F6F6'})))\n",
    "\n",
    "    return pn.Column(*panels)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ffec1d9f3cbe24d",
   "metadata": {
    "id": "7ffec1d9f3cbe24d"
   },
   "source": [
    "**Note:** The database information will be provided to the LLM using DBML, an expressive Domain-Specific Language (DSL) designed for describing database structures. [DBML](https://dbml.dbdiagram.io/docs/) is human-readable and well understood by modern Large Language Models (LLMs) as well, enabling the rich description of databases while using fewer tokens."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "922f8d24",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17
    },
    "id": "922f8d24",
    "outputId": "73379791-f1e9-4810-9fdb-2b0365bf7039"
   },
   "outputs": [],
   "source": [
    "context = [ {'role':'system', 'content':\"\"\"\n",
    "you are a bot to assist in create SQL commands, all your answers should start with \\\n",
    "this is your SQL, and after that an SQL that can do what the user request. \\\n",
    "Your Database is composed by a SQL database with some tables. \\\n",
    "Try to Maintain the SQL order simple.\n",
    "Put the SQL command in white letters with a black background, and just after \\\n",
    "a simple and concise text explaining how it works.\n",
    "If the user ask for something that can not be solved with an SQL Order \\\n",
    "just answer something nice and simple, maximum 10 words, asking him for something that \\\n",
    "can be solved with SQL.\n",
    "\"\"\"} ]\n",
    "\n",
    "context.append( {'role':'system', 'content':\"\"\"\n",
    "This is the definition of your database tables:\n",
    "\n",
    "```dbml\n",
    "# first table\n",
    "Table employees {\n",
    "  ID_usr int [pk]\n",
    "  name string\n",
    "}\n",
    "\n",
    "#second table\n",
    "Table salary {\n",
    "  ID_usr int [ref: > employees.ID_usr]\n",
    "  year date\n",
    "  salary float\n",
    "}\n",
    "\n",
    "# third table\n",
    "Table studies {\n",
    "  ID int [pk]\n",
    "  ID_usr int [ref: > employees.ID_usr]\n",
    "  educational_level int\n",
    "  Institution string\n",
    "  Years date\n",
    "  Speciality string\n",
    "}\n",
    "```\n",
    "\"\"\"\n",
    "})\n",
    "\n",
    "#Creamos el panel.\n",
    "pn.extension()\n",
    "\n",
    "panels = []\n",
    "\n",
    "client_prompt = pn.widgets.TextInput(value=\"Hi\", placeholder='Order your data…')\n",
    "button_conversation = pn.widgets.Button(name=\"generate SQL\")\n",
    "\n",
    "interactive_conversation = pn.bind(add_prompts_conversation, button_conversation)\n",
    "\n",
    "dashboard = pn.Column(\n",
    "    client_prompt,\n",
    "    pn.Row(button_conversation),\n",
    "    pn.panel(interactive_conversation, loading_indicator=True, height=300),\n",
    ")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88db4691",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 399
    },
    "id": "88db4691",
    "outputId": "0a6d5382-0835-4092-ad32-c5a515a7e1a4"
   },
   "outputs": [],
   "source": [
    "dashboard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5334f942",
   "metadata": {
    "id": "5334f942"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "include_colab_link": true,
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
